/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.ce.daoimplementation;

import com.bionic.ce.beans.Currency;
import com.bionic.ce.beans.Officialbankrate;
import com.bionic.ce.dao.IOfficialbankrateDAO;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLOfficialbankrateDAO extends MYSQLDAO implements IOfficialbankrateDAO {

    @Override
    public void add(Officialbankrate officialbankrate) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO officialbankrate "
                    + "(`CurrencyFrom`, `CurrencyTo`, `Rate`, `Date`) "
                    + "VALUES(?,?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);

            String shortname = officialbankrate.getCurrencyFrom();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            boolean b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(1, officialbankrate.getCurrencyFrom());
            } else {
                throw new SQLException("Such currency with shortname=" + officialbankrate.getCurrencyFrom() + " doesnt exists");
            }
            shortname = officialbankrate.getCurrencyTo();
            currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(2, officialbankrate.getCurrencyTo());
            } else {
                throw new SQLException("Such currency with shortname=" + officialbankrate.getCurrencyTo() + " doesnt exists");
            }
            ptmt.setDouble(3, officialbankrate.getRate());
            ptmt.setDate(4, officialbankrate.getDate());

            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Officialbankrate officialbankrate) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE officialbankrate SET `CurrencyFrom`=?, `CurrencyTo`=?, `Rate`=?, `Date`=?, `CorrespondentBank`=?"
                    + "WHERE `id`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);

            String shortname = officialbankrate.getCurrencyFrom();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            boolean b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(1, officialbankrate.getCurrencyFrom());
            } else {
                throw new SQLException("Such currency with shortname=" + officialbankrate.getCurrencyFrom() + " doesnt exists");
            }
            shortname = officialbankrate.getCurrencyTo();
            currency = new Currency();
            currency.setShortName(shortname);
            b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(2, officialbankrate.getCurrencyTo());
            } else {
                throw new SQLException("Such currency with shortname=" + officialbankrate.getCurrencyTo() + " doesnt exists");
            }
            ptmt.setDouble(3, officialbankrate.getRate());
            ptmt.setDate(4, officialbankrate.getDate());

            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteById(int id) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM officialbankrate WHERE `id`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, id);
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Officialbankrate> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Officialbankrate> result = new ArrayList();
            String queryString = "SELECT * FROM officialbankrate";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Officialbankrate officialbankrate = new Officialbankrate();
                officialbankrate.setOfficialbankrateId(resultSet.getInt("id"));
                officialbankrate.setCurrencyFrom(resultSet.getString("CurrencyFrom"));
                officialbankrate.setCurrencyTo(resultSet.getString("CurrencyTo"));
                officialbankrate.setRate(resultSet.getDouble("Rate"));
                officialbankrate.setDate(resultSet.getDate("Date"));
                result.add(officialbankrate);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Officialbankrate> findByDynamicSelect(Officialbankrate officialbankrate) throws SQLException {
        connection = getConnection();
        try {
            List<Officialbankrate> result = new ArrayList();
            String queryString = "SELECT * FROM officialbankrate WHERE ";
            int i = 0;
            if (officialbankrate.getOfficialbankrateId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + officialbankrate.getOfficialbankrateId();
                i++;
            }
            if (officialbankrate.getCurrencyFrom() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + officialbankrate.getCurrencyFrom();
                i++;
            }
            if (officialbankrate.getCurrencyFrom() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`CurrencyFrom`=\"" + officialbankrate.getCurrencyFrom() + "\"";
                i++;
            }
            if (officialbankrate.getCurrencyTo() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`CurrencyTo`=\"" + officialbankrate.getCurrencyTo() + "\"";
                i++;
            }
            if (officialbankrate.getRate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Rate`=\"" + officialbankrate.getRate() + "\"";
                i++;
            }
            if (officialbankrate.getDate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Date`=\"" + officialbankrate.getDate() + "\"";
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                officialbankrate = new Officialbankrate();
                officialbankrate.setOfficialbankrateId(resultSet.getInt("id"));
                officialbankrate.setCurrencyFrom(resultSet.getString("CurrencyFrom"));
                officialbankrate.setCurrencyTo(resultSet.getString("CurrencyTo"));
                officialbankrate.setRate(resultSet.getDouble("Rate"));
                officialbankrate.setDate(resultSet.getDate("Date"));
                result.add(officialbankrate);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }
}
